package jdbc;

import bean.User;
import utils.JDBCUtilsOld;
import org.junit.Test;

import java.sql.*;

/**
 * Date:2022/2/28
 * Author:ybc
 * Description:
 * Statement预编译对象的问题：
 * 1、拼接SQL不易
 * 2、SQL注入，即通过一些特殊的值使当前SQL的条件成立
 * 3、因为SQL只能拼接，因此不能为blob类型的字段赋值
 *
 * PreparedStatement的优点：
 * 1、可以手动拼接SQL，也可以使用通配符拼接SQL
 * 在SQL语句的需要拼接数据的位置使用?通配符
 * 之后使用PreparedStatement所提供的setXxx(count,value)为通配符赋值
 * 注意：为通配符赋值时，会自动为所有类型的数据自动添加''
 * 2、可以防止SQL注入
 * 3、可以通过setBlob()为blob类型的字段赋值
 */
public class PreparedStatementTest {

    @Test
    public void testInsertNew(){
        User user = new User(null, "小明", "123356", 23, "男", "123@qq.com");
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            String sql = "insert into t_user values(null,?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            //为SQL中的通配符赋值
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setInt(3, user.getAge());
            ps.setString(4, user.getSex());
            ps.setString(5, user.getEmail());
            int result = ps.executeUpdate();
            System.out.println("结果："+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, ps, connection);
        }
    }

    @Test
    public void testInsert(){
        User user = new User(null, "小明", "123356", 23, "男", "123@qq.com");
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            statement = connection.createStatement();
            String sql = "insert into t_user values(null, '"+user.getUsername()+"','"+user.getPassword()+"',"+user.getAge()+",'"+user.getSex()+"','"+user.getEmail()+"')";
            int result = statement.executeUpdate(sql);
            System.out.println("结果："+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, statement, connection);
        }
    }

}
